﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Orm;
using System.Data.SqlClient;
using Orm.LinqParse;
using RgUtility.Orm.Tool;
using RgUtility.Orm;
using RgUtility.Orm.Types;
namespace RgUtility.Orm
{
    /// <summary>
    /// 表上下文
    /// </summary>
    /// <typeparam name="Table">要操作的数据类。</typeparam>
    public class TableContext<Table> where Table : new()
    {
        private DbContext _innerDatabase;
        private Predicate _where;//Where语句
        private StringBuilder _orderBy;//OrderBy语句
        private int? _topNumber;
        private bool _distinct;//是否Distinct
        private List<object> _paraValues;//参数值列表

        internal TableContext(DbContext database)
        {
            this._innerDatabase = database;

            Init();
        }

        /// <summary>
        /// WHERE 条件表达式。
        /// </summary>
        /// <param name="predicate">表达式。</param>
        /// <returns></returns>
        public TableContext<Table> Where(Expression<Func<Table, bool>> predicate)
        {
            if (predicate == null)
                throw new ArgumentException("该参数不能为空！", "predicate");

            if (this._where.SqlText.Count > 0)
                this._where.SqlText.Enqueue(" AND ");

            WhereTranslator qt = new WhereTranslator();
            qt.Translate(predicate,
                (sqlText) =>
                {
                    this._where.SqlText.Enqueue(sqlText);
                    return sqlText;
                },
                (paraValue) =>
                {
                    return AddParameter(paraValue);
                });
            return this;
        }

        /// <summary>
        /// 添加参数。
        /// </summary>
        /// <param name="paraValue">参数值。</param>
        /// <returns>参数名称。</returns>
        private string AddParameter(object paraValue)
        {
            _paraValues.Add(paraValue);
            if (paraValue == null)
            {
                return "NULL";
            }
            return "@" + (_paraValues.Count - 1);
        }

        /// <summary>
        /// 添加参数。
        /// </summary>
        /// <typeparam name="T">模型。</typeparam>
        /// <param name="t">模型实例。</param>
        /// <param name="name">字段名称。</param>
        /// <returns>参数名称。</returns>
        private string AddParameter<T>(T t, string name)
        {
            var type = typeof(T);
            var p = type.GetProperty(name);
            return AddParameter(p.GetValue(t, null));
        }

        /// <summary>
        /// 正向排序。
        /// </summary>
        /// <typeparam name="TKey"></typeparam>
        /// <param name="keySelector">排序字段表达式。</param>
        /// <returns></returns>
        public TableContext<Table> OrderBy<TKey>(Expression<Func<Table, TKey>> keySelector)
        {
            if (keySelector == null)
                throw new ArgumentException("该参数不能为空！", "keySelector");
            //获取显示的字段列表
            List<string> orderByNames = new FieldNameTranslator().GetName(keySelector.Body, false, false);
            if (orderByNames.Count > 0)
            {
                if (this._orderBy.Length > 0)
                    this._orderBy.Append(",");
                this._orderBy.Append(orderByNames.ToCSV(" asc,",false));
                this._orderBy.Append(" asc");
            }

            return this;
        }

        /// <summary>
        /// 逆向排序。
        /// </summary>
        /// <typeparam name="TKey"></typeparam>
        /// <param name="keySelector">排序字段表达式。</param>
        /// <returns>查询结果列表。</returns>
        public TableContext<Table> OrderByDescending<TKey>(Expression<Func<Table, TKey>> keySelector)
        {
            if (keySelector == null)
                throw new ArgumentException("该参数不能为空！", "keySelector");

            //获取显示的字段列表
            List<string> orderByNames = new FieldNameTranslator().GetName(keySelector.Body, false, false);
            if (orderByNames.Count > 0)
            {
                if (this._orderBy.Length > 0)
                    this._orderBy.Append(",");

                this._orderBy.Append(orderByNames.ToCSV(" desc,",false));
                this._orderBy.Append(" desc");
            }
            return this;
        }

        /// <summary>
        /// 指示只能从查询结果集返回指定的一组行。
        /// </summary>
        /// <param name="topNumber">指定的行数目。</param>
        /// <returns></returns>
        public TableContext<Table> Top(int topNumber)
        {
            this._topNumber = topNumber;
            return this;
        }

        /// <summary>
        /// 筛选字段中不相同的值,用于查询不重复的结果集。
        /// </summary>
        /// <returns></returns>
        public TableContext<Table> Distinct()
        {
            this._distinct = true;
            return this;
        }

        /// <summary>
        /// 获取查询结果的总条数。
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }

            var rtn = this._innerDatabase.Query<TValue<int>>(BuildSqlCount(), sqlParaLs.ToArray())
                .FirstOrDefault().Value;
            Init();
            return rtn;
        }

        /// <summary>
        /// 获取查询结果。
        /// <code>.Select(s=>s);  或 .Select(s=> new{s.Field1,s.Field2,...});</code>
        /// </summary>
        /// <typeparam name="TSelectFields"></typeparam>
        /// <param name="selectFields">在select中出现的字段列表。</param>
        /// <returns></returns>
        public List<Table> Select<T_SelectFields>(Expression<Func<Table, T_SelectFields>> selectFields)
        {
            if (selectFields == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectFields.Body).ToArray();
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<Table>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray());
            Init();
            return rtn;
        }

        /// <summary>
        /// 获取查询结果。
        /// <code>.Select(s=>s);  或 .Select(s=> new{s.Field1,s.Field2,...});</code>
        /// </summary>
        /// <typeparam name="TSelectFields"></typeparam>
        /// <param name="selectFields">在select中出现的字段列表。</param>
        /// <param name="pager">分页信息。</param>
        /// <returns></returns>
        public List<Table> Select<T_SelectFields>(Expression<Func<Table, T_SelectFields>> selectFields, Pager pager)
        {
            if (selectFields == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            if (pager != null)
            {
                if (this._orderBy.Length <= 0)
                    throw new ArgumentException("分页必须指定排序字段！");
            }
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            //计算总的记录数
            pager.RecordCount = this._innerDatabase.Query<TValue<int>>(BuildSqlCount(), sqlParaLs.ToArray())
                .FirstOrDefault().Value;
            pager.PageCount = pager.RecordCount % pager.PageSize != 0 ? 
                pager.RecordCount / pager.PageSize+1
                : pager.RecordCount / pager.PageSize+2;

            //如果没有查询到记录，则不进行数据库分页查询，直接返回空列表。
            if (pager.RecordCount <= 0)
            {
                Init();
                return new List<Table>();
            }

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectFields.Body).ToArray();
            var sql = BuildSqlSelect(selectNames, pager);
            sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<Table>(sql, sqlParaLs.ToArray());
            Init();
            return rtn;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="data">要更新的数据</param>
        /// <param name="updateFields">指定要更新的字段（不可null）</param>
        /// <param name="ignoreUpdateFields">要忽略的字段（可null）</param>
        /// <returns>影响行数</returns>
        public int Update(Table data, Expression<Func<Table, object>> updateFields, Expression<Func<Table, object>> ignoreUpdateFields=null)
        {
            if (updateFields == null)
                throw new ArgumentException("该参数不能为空！");
            //获取更新的字段列表
            List<string> updateNames = new FieldNameTranslator().GetName(updateFields.Body, true, true);
            if (ignoreUpdateFields != null)
            {
                List<string> ignoreUpdateNames = new FieldNameTranslator().GetName(ignoreUpdateFields.Body, true, true);
                updateNames = updateNames.Except(ignoreUpdateNames).ToList();
            }


            if (updateNames.Count <= 0) return 0;

            //构建SQL
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            sb.Append(GetTableName(data.GetType()));
            sb.Append(" SET ");
            sb.Append(updateNames.Select(
                f => f + "=" + AddParameter(data, f)).ToCSV(",",false));

            //where 条件
            if (this._where.SqlText.Count > 0)
            {
                sb.Append(" WHERE ");
                sb.Append(_where.GetSqlText());
            }
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                var item = _paraValues[i];
                if (item != null)
                {
                    sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
                }
            }
            var rtn = this._innerDatabase.NonQuery(sb.ToString(), sqlParaLs.ToArray());
            Init();
            return rtn;
        }

        /// <summary>
        /// 删除数据。
        /// </summary>
        /// <returns></returns>
        public int Delete()
        {
            //构建SQL
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE FROM  ");
            sb.Append(GetTableName(typeof(Table)));

            //where 条件
            if (this._where.SqlText.Count > 0)
            {
                sb.Append(" WHERE ");
                sb.Append(_where.GetSqlText());
            }
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                var item = _paraValues[i];
                if (item != null)
                {
                    sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
                }
            }
            var rtn = this._innerDatabase.NonQuery(sb.ToString(), sqlParaLs.ToArray());
            Init();
            return rtn;
        }

        /// <summary>
        /// 添加一条数据
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="insertFields">添加那些列（不能null）</param>
        /// <param name="ignoreInsertFields">忽略那些列（可null可覆盖前者）</param>
        /// <param name="isReturnKey">是否返回@@identity</param>
        /// <returns>默认返回受影响行数，实际结果取决于最后一个参数</returns>
        public int Add(Table data, Expression<Func<Table, object>> insertFields, Expression<Func<Table, object>> ignoreInsertFields=null, bool isReturnIdentity = false)
        {
            if (insertFields == null)
                throw new ArgumentException("该参数不能为空！", "insertFields");
            //获取插入的字段列表
            List<string> insertNames = new FieldNameTranslator().GetName(insertFields.Body, true, true);
            if (ignoreInsertFields != null)
            {
                List<string> ignoreUpdateNames = new FieldNameTranslator().GetName(ignoreInsertFields.Body, true, true);
                insertNames = insertNames.Except(ignoreUpdateNames).ToList();
            }
            if (insertNames.Count <= 0) return 0;
            //构建SQL
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO ");
            sql.Append(GetTableName(data.GetType()));
            sql.Append(" (");
            sql.Append(insertNames.ToCSV(",",true));
            sql.Append(") VALUES (");
            sql.Append(insertNames.Select(
                f => AddParameter(data, f)).ToCSV(",",false));
            sql.Append(")");
            //执行
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                var item = _paraValues[i];
                if (item != null)
                {
                    sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
                }
            }
            var rtn = this._innerDatabase.NonQuery(sql.ToString(), sqlParaLs.ToArray());
            if (isReturnIdentity)
            {
                var id = _innerDatabase.Query<TValue<decimal>>
                    ("select @@identity Value;", new SqlParameter[] { })
                    .FirstOrDefault().Value;
                rtn = (int)id;
            }
            Init();
            return rtn;
        }

        private string BuildSqlSelect(string[] selectNames, Pager pager)
        {
            //构建SQL
            StringBuilder sb = new StringBuilder();
            //分页
            if (pager != null)
            {
                sb.Append("SELECT ");
                sb.Append(selectNames.ToCSV(",",true));
                sb.Append(" FROM (select *, ROW_NUMBER() OVER (ORDER BY ");
                sb.Append(_orderBy);
                sb.Append(") AS pt_rowNum FROM (");
            }
            sb.Append("SELECT ");
            if (this._distinct)
                sb.Append(" DISTINCT ");
            if (this._topNumber.HasValue)
                sb.Append(string.Format("TOP ({0}) ", this._topNumber.Value.ToString()));
            sb.Append(selectNames.ToCSV(",",true));
            sb.Append(" FROM ");
            sb.Append(GetTableName(typeof(Table)));
            //where 条件
            if (this._where.SqlText.Count > 0)
            {
                sb.Append(" WHERE ");
                sb.Append(_where.GetSqlText());
            }
            //分页
            if (pager != null)
            {
                sb.Append(") page_t1) page_t2");
                sb.Append(" WHERE pt_rowNum BETWEEN ");
                sb.Append(AddParameter(pager.PageIndex * pager.PageSize + 1));
                sb.Append(" AND ");
                sb.Append(AddParameter(pager.PageIndex+1 * pager.PageSize));
                sb.Append(" ORDER BY pt_rowNum");
            }
            //order by
            if (pager == null && this._orderBy.Length > 0)
            {
                sb.Append(" ORDER BY ");
                sb.Append(_orderBy);
            }
            return sb.ToString();
        }

        private string BuildSqlCount()
        {
            //构建SQL
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT COUNT(*) Value FROM ");
            sb.Append(GetTableName(typeof(Table)));


            //where 条件
            if (this._where.SqlText.Count > 0)
            {
                sb.Append(" WHERE ");
                sb.Append(_where.GetSqlText());
            }
            return sb.ToString();
        }

        /// <summary>
        /// 重置表达式内容。
        /// </summary>
        private void Init()
        {
            this._where = new Predicate();
            this._orderBy = new StringBuilder();
            this._topNumber = null;
            this._distinct = false;
            this._paraValues = new List<object>();
        }

        /// <summary>
        /// 获取表名。
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetTableName(Type type)
        {
            #region
            //string tableName = string.Empty; ;
            //var tableAttribute = type.GetCustomAttributes(typeof(TableNameAttribute), false);
            //if (tableAttribute.Length > 0)
            //{
            //    tableName = ((TableNameAttribute)tableAttribute[0]).Value;
            //}
            //else
            //{
            //    throw new Exception("未标注表名！");
            //}
            #endregion
            return "[" + type.Name + "]";
        }

        #region 聚合函数
        public int? Max(Expression<Func<Table, int>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("max(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<int>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }
        public decimal? Max(Expression<Func<Table, decimal>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("max(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<decimal>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }
        public DateTime? Max(Expression<Func<Table, DateTime>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("max(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<DateTime>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }

        public int? Min(Expression<Func<Table, int>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("min(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<int>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }
        public decimal? Min(Expression<Func<Table, decimal>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("Min(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<decimal>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }
        public DateTime? Min(Expression<Func<Table, DateTime>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("min(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<DateTime>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }

        public int? Sum(Expression<Func<Table, int>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("sum(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<int>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }
        public decimal? Sum(Expression<Func<Table, decimal>> selectField)
        {
            if (selectField == null)
                throw new ArgumentException("该参数不能为空！", "selectFields");

            //获取显示的字段列表
            var selectNames = new SelectTranslator().GetName(selectField.Body).ToArray();
            if (selectNames.Count() != 1)
            {
                throw new ArgumentException("只能指定一个聚合字段！");
            }
            selectNames[0] = ("sum(" + selectNames[0] + ") Value");
            var sqlParaLs = new List<SqlParameter>();
            for (int i = 0; i < this._paraValues.Count; i++)
            {
                sqlParaLs.Add(new SqlParameter("@" + i, this._paraValues[i]));
            }
            var rtn = this._innerDatabase.Query<TValue<decimal>>(BuildSqlSelect(selectNames, null), sqlParaLs.ToArray())
                .FirstOrDefault();
            Init();
            if (rtn == null)
            {
                return null;
            }
            return rtn.Value;
        }

        #endregion
    }
}